package com.apes.scm.invoice.repository;

import com.alibaba.fastjson.JSONArray;
import com.apes.framework.query.ApesDao;
import org.springframework.stereotype.Component;

import java.util.Date;
import java.util.List;
import java.util.Map;

@Component
public class InvoiceltemDao extends ApesDao {
    public List<Map> findCreateDate(String createDate) {
        String sql = "SELECT a.company_id, a.partner_id,a.dept_id, a.inventory_mode, sum(b.discount) as discount,b.tax_id\n" +
                "  FROM acc_invoice a, acc_invoice_item b\n" +
                " where a.id = b.invoice_id\n" +
                "   and b.discount > 0\n" +
                "   and a.company_id in ('1001', '1007')\n" +
                "   and a.create_date >= to_date('" + createDate + "','yyyy-MM')\n" +
                " group by a.company_id, a.partner_id, a.inventory_mode,b.tax_id,a.dept_id\n";
        return find(sql);
    }

    public List<Map> findIncome(String createDate) {
        String sql = "SELECT *\n" +
                "  FROM pur_income a\n" +
                " where a.create_date >= to_date('" + createDate + "', 'yyyy-MM')\n" +
                "   and a.note = '供应商账单定时生成'\n";
        return find(sql);
    }

    public List<Map> busines(String id) {
        String sql = "SELECT a.query_key,a.value,a.value2 FROM mas_Business_Param a where a.id = " + id;
        return find(sql);
    }

    public JSONArray findServicePlan(String id) {
        String sql = "SELECT (select c.name from MAS_CHANNEL C where c.ID = A.CHANNEL) CHANNEL_NAME\n" +
                "  FROM SER_SERVICE_PLAN a where a.id='" + id + "'";
        return toJSONArray(this.find(sql));
    }

    public JSONArray findOperatorProduct(String operator,String product) {
        String sql = "SELECT A.STATE, B.OPERATOR_ID, B.P117, B.P116, B.PRODUCT_ID,\n" +
                "       (SELECT S0.NAME FROM MAS_BRAND S0, MAS_PRODUCT S1 WHERE 1 = 1 AND S0.ID = S1.BRAND_ID\n" +
                "            AND S1.ID = B.PRODUCT_ID) PRODUCT_ID_BRAND_ID_NAME,\n" +
                "       (SELECT S0.BRAND_ID FROM MAS_PRODUCT S0 WHERE 1 = 1 AND S0.ID = B.PRODUCT_ID) PRODUCT_ID_BRAND_ID\n" +
                "  FROM Z_T14398 A, Z_T14398_ITEM B WHERE A.ID = B.ID and a.state = 'exec' and a.END_DATE >= sysdate\n" +
                "   and b.operator_id = '"+operator+"' and b.product_id = '"+product+"'\n" +
                "   and (SELECT S0.BRAND_ID FROM MAS_PRODUCT S0 WHERE 1 = 1 AND S0.ID = B.PRODUCT_ID) = '014520' " +
                "   order by a.create_date desc";
        return toJSONArray(this.find(sql));
    }


    public JSONArray maintenanceOrder(String id) {
        String sql = "SELECT B.PFT_PRICE_UNIT,A.SALE_QTY,a.PLATFORM_PURCHASE_COST,b.SALES_AMOUNT, A.PLATFORM_TAX_TEMP,a.PLATFORM_EX\n" +
                "  FROM SER_MAINTENANCE_ORDER a LEFT JOIN SER_SERVICE_PLAN_ITEM b\n" +
                "    ON A.SERVICE_PLAN_ITEM = B.ID  where a.id = '" + id + "'";
        return toJSONArray(this.find(sql));
    }
}
